import tkinter as tk
from tkinter import scrolledtext
import pymssql

def open(s):
    window = tk.Tk()
    window.title("养护人员-" + s + "---修改")
    window.geometry("600x300")
#设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="养护任务编号：")
    label1.grid(row=0, column=0)
    entry1 = tk.Entry(window)
    entry1.grid(row=0, column=1)
    label2 = tk.Label(window, text="需要修改的信息：")
    label2.grid(row=1, column=0)
    entry2 = tk.Entry(window)
    entry2.grid(row=1, column=1)
    conn = pymssql.connect(host='localhost', server='DESKTOP-NBNT8AG', port='1433', user='lxy',
                           password='lxy618',
                           database='植物管理系统', charset='utf8')
    if conn:
        print("连接成功！")

    def set1():
        message = entry1.get()
        new_message = entry2.get()
        cursor = conn.cursor()  # 获取游标

        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{new_message}'"
        cursor.execute(sql)
        result1 = ""

        for row in cursor:  # 遍历输出
            result1 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
            row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                    row[3]) + "  " + "养护任务执行人员：" + "%s" % (row[4]) + "  " + "养护任务描述：" + "%s" % (
                        row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (row[7])
            print(result1)

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                           row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                           row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务编号='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set2():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务名称='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set3():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务执行地点='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set4():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务执行时间='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set5():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务执行人员='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set6():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务描述='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set7():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护对象='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    def set8():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 养护 WHERE 养护.养护任务编号='{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result2 = "养护任务不存在"
            scrollbar.insert(tk.INSERT, result2)
        else:
            for row in cursor:  # 遍历输出
                result2 += "养护任务编号：" + "%s" % (row[0]) + "  " + "养护任务名称：" + "%s" % (
                    row[1]) + "  " + "养护任务执行地点：" + "%s" % (row[2]) + "  " + "养护任务执行时间：" + "%s" % (
                               row[3]) + "  " + "养护任务执行人员：" + "%s" % (
                               row[4]) + "  " + "养护任务描述：" + "%s" % (
                               row[5]) + "  " + "养护对象：" + "%s" % (row[6]) + "  " + "养护任务执行状态：" + "%s" % (
                               row[7])
                print(result2)

            scrollbar.insert(tk.INSERT, result2)

            cursor = conn.cursor()
            sql = f"UPDATE 养护 SET 养护.养护任务执行状态='{new_message}' FROM 养护 WHERE 养护.养护任务编号='{message}'"
            cursor.execute(sql)

            conn.commit()

    button1 = tk.Button(window, text="养护任务编号", command=lambda: set1(), width=20, height=1)
    button2 = tk.Button(window, text="养护任务名称", command=lambda: set2(), width=20, height=1)
    button3 = tk.Button(window, text="养护任务执行地点", command=lambda: set3(), width=20, height=1)
    button4 = tk.Button(window, text="养护任务执行时间", command=lambda: set4(), width=20, height=1)
    button5 = tk.Button(window, text="养护任务执行人员", command=lambda: set5(), width=20, height=1)
    button6 = tk.Button(window, text="养护任务描述", command=lambda: set6(), width=20, height=1)
    button7 = tk.Button(window, text="养护对象", command=lambda: set7(), width=20, height=1)
    button8 = tk.Button(window, text="养护任务执行状态", command=lambda: set8(), width=20, height=1)

    return_button = tk.Button(window, text="返回", command=window.destroy, width=20, height=1)

    button1.grid(row=2, column=0)
    button2.grid(row=2, column=1)
    button3.grid(row=2, column=2)
    button4.grid(row=3, column=0)
    button5.grid(row=3, column=1)
    button6.grid(row=3, column=2)
    button7.grid(row=4, column=0)
    button8.grid(row=4, column=1)

    return_button.grid(row=4, column=2, sticky="nsew")
    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass